/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.QuestionCategory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import util.DBUtil;

/**
 *
 * @author PhucAn
 */
public class QuestionCategoryDAO {

    Connection conn = null;
    Logger log = Logger.getLogger(QuestionCategory.class);
    private final String sqlSelectById = "select * from Question_Category where Id=?";
    private final String sqlSelectAll = "select * from Question_Category";
    private final String sqlCreate = "insert into Question_Category([name],description) values(?,?)";
    private final String sqlUpdate = "update Question_Category set [name]=?, Description=? where Id=?";
    private final String sqlDelete = "delete from Question_Category where id=?";

    /**
     * Get a category by specifying identifier
     * @param id
     * @return 
     */
    public QuestionCategory getCategory(int id) {
        QuestionCategory category = new QuestionCategory();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return category;
            }
            PreparedStatement ps = conn.prepareStatement(sqlSelectById);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                category.setId(id);
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
            }
        } catch (SQLException ex) {
            log.info("Error: " + ex.getMessage());
        } catch (Exception ex) {
            log.info("Error: " + ex.getMessage());
        }
        return category;
    }

    public ArrayList<QuestionCategory> getAllCategories() {
        ArrayList<QuestionCategory> list = new ArrayList<QuestionCategory>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return list;
            }
            PreparedStatement ps = conn.prepareStatement(sqlSelectAll);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                QuestionCategory category = new QuestionCategory();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setDescription(rs.getString("description"));
                list.add(category);
            }
        } catch (SQLException ex) {
            log.info("Error: " + ex.getMessage());
        } catch (Exception ex) {
            log.info("Error: " + ex.getMessage());
        }
        return list;
    }

    public int create(QuestionCategory category) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setString(1, category.getName());
            ps.setString(2, category.getDescription());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            log.info(ex.getMessage());
        } catch (Exception ex) {
            log.info("Error: " + ex.getMessage());
        }
        return effectedRows;
    }

    public int update(QuestionCategory category) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, category.getName());
            ps.setString(2, category.getDescription());
            ps.setInt(3, category.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            log.info(ex.getMessage());
        } catch (Exception ex) {
            log.info("Error: " + ex.getMessage());
        }
        return effectedRows;
    }

    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            log.info(ex.getMessage());
        } catch (Exception ex) {
            log.info("Error: " + ex.getMessage());
        }
        return effectedRows;
    }
}
